Amazon Mobile AnalyticsでエクスポートしたS3のデータを、Amazon Athenaで集計する #serverless #adventcalendar
はじめに
このエントリは Serverless Advent Calendar 2017 15日目の記事です。
Amazon Mobile Analytics はアプリケーションの利用に関する情報を収集・分析するためのサービスです。Auto Export機能を有効にすると、分析用のデータをS3およびRedshiftにエクスポートできます。高頻度でクエリを実行する場合はRedshiftが便利ですが、1日に数回クエリをかけるぐらいの利用頻度だとオーバースペックかもしれません。
今回はサーバレスなクエリサービス Amazon Athena を使って、 S3にエクスポートしたデータにクエリをかけてみます。
Amazon Mobile Analyticsのデータ
Amazon Mobile AnalyticsでS3へのAuto Export機能を有効にすると、任意のバケットにJSON形式のファイルを定期的に出力します。
今回は確認用のWEBページを用意してカスタムイベントを送信しました。
イベント送信処理
mobileAnalyticsClient.recordEvent('content-loaded', {
'custom-id': 1, 'custom-name': 'index_page',
}, {});
mobileAnalyticsClient.submitEvents();
mobileAnalyticsClient.recordEvent('content-loaded', {
'custom-id': 2, 'custom-name': 'another_page',
}, {});
mobileAnalyticsClient.submitEvents();
既定の項目に加えて、 custom-id と custom-name を記録します。
Amazon Athenaでクエリを実行する
eventsテーブルを作成
Create Table から Manually を選択してテーブルを定義します。
テーブルの基本情報
- Database : データベース名
- Table Name : テーブル名
- Location of Input Data Set : エクスポート先のS3のURL
今回はデータベース名を awsma 、 テーブル名を events に設定します。
入力データのフォーマット
入力データのフォーマットは JSON を選択します。
構成列を定義
S3に出力されるJSONのフォーマットは、公式ドキュメントに記載されています。
各項目を参照できるように、テーブルの構成列を定義します。 application や client など、入れ子のJSONを含むため、下記いずれかの方法で内側の項目を参照できるようにします。
- MAP型やSTRUCT型で定義する
- STRING型として定義して、射影するときに
json_extract_scalar関数を使う
今回は後者の方法を試してみます。前者の方法は下記の記事を参照してください。
構成列が多いので、 Bulk add columns で一括定義します。
下記の列を定義します。
event_type string, event_timestamp bigint, arrival_timestamp bigint, event_version string, application string, client string, device string, session string, monetization string, attributes string, metrics string
パーティション列を定義
S3にエクスポートされるデータは、下記の構造で配置されます。
年月日・時間ごとのフォルダを参照するため、パーティション列を追加します。今回は created_at 列として追加します。
パーティションについては下記の記事を参照してください。
ここまでで events テーブルの作成完了です。
パーティションの追加
S3にエクスポートされるデータは、Amazon Athenaが期待するパーティションの形式 <パーティション列>=<値> と異なる構造で配置されるので、 ALTER TABLE ADD PARTITION で明示的に配置場所を指定してパーティションを追加します。
ALTER TABLE awsma.events ADD PARTITION (created_at='2017-12-13_01') LOCATION 's3:///awsma/events//2017/12/13/01' PARTITION (created_at='2017-12-13_02') LOCATION 's3:///awsma/events//2017/12/13/02' ;
クエリ実行
検索できるようになったか確認します。
生データの確認
SELECT * FROM awsma.events WHERE created_at BETWEEN '2017-12-13_01' AND '2017-12-13_02' ;
定義した通りの内容で検索できるようになりました。
各項目を検索
タイムスタンプや入れ子のJSONはそのままだと確認しづらいので、わかりやすい形に変換します。
SELECT from_unixtime(event_timestamp % 1000) AT TIME ZONE 'Asia/Tokyo' event_timestamp, -- (1) タイムスタンプ変換 from_unixtime(arrival_timestamp % 1000) AT TIME ZONE 'Asia/Tokyo' arrival_timestamp, json_extract_scalar(application, '$.sdk.name') sdk_name, -- (2) 入れ子JSONから任意の項目取り出し json_extract_scalar(application, '$.sdk.version') sdk_version, json_extract_scalar(device, '$.platform.name') platform_name, json_extract_scalar(device, '$.platform.version') platform_version, json_extract_scalar(attributes, '$["custom-id"]') custom_id, -- (3) 入れ子JSONから任意の項目取り出し json_extract_scalar(attributes, '$["custom-name"]') custom_name FROM awsma.events WHERE created_at BETWEEN '2017-12-13_01' AND '2017-12-13_02' ORDER BY event_timestamp ;
- (1) エポックからの経過ミリ秒をタイムスタンプに変換します。今回はミリ秒以下を切り捨てていますが、 parse_duration 関数と組み合わせるとミリ秒も含めることができます。
- (2) json_extract_scalar 関数を使って任意の項目を取り出します。
- 第1引数: 列名を指定します
- 第2引数: JSONPath 形式で指定します
- (3) (2)と同様ですが、
.区切りで指定できない文字-を含むので、キーを文字列で指定してアクセスします。
カスタムイベントを集計
カスタムイベントのイベント名ごとに数を集計します。
SELECT q.custom_name, COUNT(1) event_count FROM ( SELECT json_extract_scalar(attributes, '$["custom-name"]') custom_name FROM awsma.events WHERE created_at BETWEEN '2017-12-13_01' AND '2017-12-13_02' AND event_type = 'content-loaded' ) q GROUP BY q.custom_name ORDER BY event_count DESC ;
おわりに
Amazon Athenaを活用して、 Amazon Mobile AnalyticsがエクスポートしたS3のデータにクエリを実行することができました。 日時のバッチ集計などにも利用できそうなので、積極的に活用していきたいですね！
